﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tools = Microsoft.Office.Tools;
using Interop = Microsoft.Office.Interop;

namespace FarpacHelper
{
    public class xls
    {
        //============================================================================================================================//
        public static Tools.Excel.Worksheet getSheet(Tools.Excel.Workbook book, string sheetName)
        {
            foreach (Interop.Excel.Worksheet sheet in book.Worksheets)
            {
                string txt = sheet.Name.ToLower();
                if (txt == sheetName.ToLower()) return Tools.Excel.Worksheet.GetVstoObject(sheet);
            }

            return null;
        }
        //============================================================================================================================//
        public bool IsNumberCell(Tools.Excel.Worksheet sheet, int row, int column)
        {
            //if (sheet.Application.WorksheetFunction.IsNumber(range) return true; else
            return false;
        }
        //============================================================================================================================//
        public static bool IsBlankCell(Tools.Excel.Worksheet sheet, int row, int column)
        {
            if (((sheet.Cells[row, column] as Interop.Excel.Range).Text as string).Trim() == "") return true;
            else return false;
        }
        //============================================================================================================================//
        public static string ReadString(Tools.Excel.Worksheet sheet, int row, int column, string valueIFfail = "")
        {
            try
            {
                return (sheet.Cells[row, column] as Interop.Excel.Range).Value2 as string; // Test Value2 or Text  is faster ? And which one read correct when 123456 i collapsed & appear ####
            }
            catch (Exception e) { return valueIFfail; }
        }
        //============================================================================================================================//
        public static double ReadDouble(Tools.Excel.Worksheet sheet, int row, int column, double valueIFfail = 0)
        {
            try
            {
                return (double)(sheet.Cells[row, column] as Interop.Excel.Range).Value2; // Test Value2 or Text  is faster ? And which one read correct when 123456 i collapsed & appear ####
            }
            catch (Exception e) { return valueIFfail; }
        }
        //============================================================================================================================//
        public static int ReadInteger(Tools.Excel.Worksheet sheet, int row, int column, int valueIFfail = 0)
        {
            try
            {
                return (int)(double)(sheet.Cells[row, column] as Interop.Excel.Range).Value2; // Test Value2 or Text  is faster ? And which one read correct when 123456 i collapsed & appear ####
            }
            catch (Exception e) { return valueIFfail; }
        }
        //============================================================================================================================//
        public static bool WriteDouble(Tools.Excel.Worksheet sheet, int row, int column, double value)
        {
            try
            {
                (sheet.Cells[row, column] as Interop.Excel.Range).Value2 = value; // Test Value2 or Text  is faster ? And which one read correct when 123456 i collapsed & appear ####
                return true;
            }
            catch { return false; }
        }
        //============================================================================================================================//
        public static bool WriteString(Tools.Excel.Worksheet sheet, int row, int column, string value = null)
        {
            try
            {
                if (value == null) (sheet.Cells[row, column] as Interop.Excel.Range).Clear();
                else if (value == string.Empty) (sheet.Cells[row, column] as Interop.Excel.Range).ClearContents();
                else (sheet.Cells[row, column] as Interop.Excel.Range).Value2 = value; // Test Value2 or Text  is faster ? And which one read correct when 123456 i collapsed & appear ####
                return true;
            }
            catch { return false; }
        }
    }
}
